/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package limousine.dbservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.DataSource;
import limousine.util.Func;
import limousine.vo.TransportTariff;

/**
 *
 * @author Tailer
 */
public class TransportTariffService {

    private DataSource ds = null;
    private Func func = new Func();

    public DataSource getDs() {
        return ds;
    }

    public void setDs(DataSource ds) {
        this.ds = ds;
    }

    public TransportTariff getByTableId(String tableId) {
        StringBuffer sql = new StringBuffer();
        sql.append("select * From TRANSPORT_TARIFF where TABLE_ID = '" + tableId.trim() + "' ");
        TransportTariff o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new TransportTariff();
                o.setTableId(rs.getString("TABLE_ID"));
                o.setTransportTariffId(rs.getString("TRANSPORTARIFF_ID"));
                o.setTariffOrder(rs.getInt("TARIFF_ORDER"));
                o.setDistination(rs.getString("DISTINATION"));
                o.setAreaType(rs.getInt("AREA_TYPE"));
                o.setAreaCover(rs.getString("AREA_COVER"));
                o.setTimeUsage(rs.getString("TIME_USAGE"));
                o.setPostalCodeCover(rs.getString("POSTAL_CODE_COVER"));
                o.setRateSedan(rs.getInt("RATE_SEDAN"));
                o.setRateVan(rs.getInt("RATE_VAN"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(rs.getTimestamp("CREATE_DATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }

    public boolean insert(TransportTariff o) {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO TRANSPORT_TARIFF(TABLE_ID,TRANSPORTARIFF_ID,TARIFF_ORDER,DISTINATION,AREA_TYPE,AREA_COVER ");
        sql.append("TIME_USAGE,POSTAL_CODE_COVER,RATE_SEDAN,RATE_VAN,CREATE_BY,CREATE_DATE,UPDATE_BY,UPDATE_DATE ) ");
        sql.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getTableId());
            stmt.setString(2, o.getTransportTariffId());
            stmt.setInt(3, o.getTariffOrder());
            stmt.setString(4, o.getDistination());
            stmt.setInt(5, o.getAreaType());
            stmt.setString(6, o.getAreaCover());
            stmt.setString(7, o.getTimeUsage());
            stmt.setString(8, o.getPostalCodeCover());
            stmt.setInt(9, o.getRateSedan());
            stmt.setInt(10, o.getRateVan());
            stmt.setString(11, o.getCreateBy());
            stmt.setTimestamp(12, func.getTimestamp());
            stmt.setString(13, o.getUpdateBy());
            stmt.setTimestamp(14, func.getTimestamp());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean update(TransportTariff o) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE TRANSPORT_TARIFF ");
        sql.append("SET TRANSPORTARIFF_ID=?,TARIFF_ORDER=?,DISTINATION=?,AREA_TYPE=?,AREA_COVER=? ");
        sql.append("TIME_USAGE=?,POSTAL_CODE_COVER=?,RATE_SEDAN=?,RATE_VAN=?,UPDATE_BY=?,UPDATE_DATE=? ");
        sql.append("where TABLE_ID='" + o.getTableId() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getTransportTariffId());
            stmt.setInt(2, o.getTariffOrder());
            stmt.setString(3, o.getDistination());
            stmt.setInt(4, o.getAreaType());
            stmt.setString(5, o.getAreaCover());
            stmt.setString(6, o.getTimeUsage());
            stmt.setString(7, o.getPostalCodeCover());
            stmt.setInt(8, o.getRateSedan());
            stmt.setInt(9, o.getRateVan());
            stmt.setString(10, o.getUpdateBy());
            stmt.setTimestamp(11, func.getTimestamp());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteByTableId(String tableId) {
        StringBuffer sql = new StringBuffer();
        sql.append("delete from TRANSPORT_TARIFF where TABLE_ID = '" + tableId.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteBySQL(String sql) {
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
}
